﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{
    public partial class VKUNDADR
    {
        public void SetUpNewRow(ref DataRow dwReturn)
        {

            switch (app.company)
            {
                case "YGR":
                case "YCC":
                case "TST":
                case "UBT":
                default:
                    #region=========
                    dwReturn["KDAMANDANT"] = app.Mandant;
                    dwReturn["KDAKUNDNR"] = "00000000";
                    dwReturn["KDAADRSL"] = "        ";
                    dwReturn["KDASTAKZ"] = "A";
                    dwReturn["KDASTADAT"] = "0";
                    dwReturn["KDAANREDE"] = " ";
                    dwReturn["KDANAME1"] = "                                        ";
                    dwReturn["KDANAME2"] = "                                        ";
                    dwReturn["KDASTRASSE1"] = "                                        ";
                    dwReturn["KDASTRASSE2"] = "                                        ";
                    dwReturn["KDALAND"] = "CN ";
                    dwReturn["KDAPLZ"] = "            ";
                    dwReturn["KDAORT"] = "¸                                       ";
                    dwReturn["KDABZ"] = "  ";
                    dwReturn["KDABZBEZ"] = "             ";
                    dwReturn["KDATEL"] = "                    ";
                    dwReturn["KDATELEX"] = "                    ";
                    dwReturn["KDAFAX"] = "                    ";
                    dwReturn["KDABTX"] = "                    ";
                    dwReturn["KDADATEX"] = "                    ";
                    dwReturn["KDAHOMEPAGE"] = "                                        ";
                    dwReturn["KDAMAILADR"] = "                                                                                                    ";
                    dwReturn["KDATTEX"] = "                    ";
                    dwReturn["KDASPRKZ"] = " ";
                    dwReturn["KDALO"] = "  ";
                    dwReturn["KDALB"] = "  ";
                    dwReturn["KDALP"] = "        ";
                    dwReturn["KDABBN"] = "                    ";
                    dwReturn["KDALIEFNR"] = "                ";
                    dwReturn["KDAPLZPOSTF"] = "            ";
                    dwReturn["KDAPOSTFACH"] = "               ";
                    dwReturn["KDACOUNTY"] = "    ";
                    dwReturn["KDACITY"] = "    ";
                    dwReturn["KDASTEUER"] = "   ";
                    dwReturn["KDAREGMWST"] = "   ";
                    dwReturn["KDAMWSTID"] = "                    ";
                    dwReturn["KDAMWSTID2"] = "                    ";
                    dwReturn["KDASPERRPJ"] = "N";
                    dwReturn["KDASTLAND"] = "  ";
                    dwReturn["KDASTREGBZ"] = "  ";
                    dwReturn["KDASTKREIS"] = "  ";
                    dwReturn["KDASTORT"] = "   ";
                    dwReturn["KDAILN"] = "             ";
                    dwReturn["KDASHIPINVD"] = "000";
                    dwReturn["KDAEORI"] = "                 ";
                    dwReturn["KDAEORINL"] = "0000";
                    dwReturn["KDAKTOGRP"] = "  ";
                    dwReturn["KDAFILLER"] = "                                                                                 ";
                    dwReturn["KDAAENDZL"] = "0";
                    dwReturn["KDAAENDBEN"] = "        ";
                    dwReturn["KDAAENDDAT"] = "0";
                    dwReturn["KDANEUBEN"] = "        ";
                    dwReturn["KDANEUDAT"] = "0";

                    #endregion
                    break;
            }
        }

        public void InsertKUNDADR(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            if (dwData != null)
            {
                dwData["KDANEUBEN"] = app.User;
                dwData["KDANEUDAT"] = date;
            }
            or1 = OracleDB.CreateCommand();
            switch (app.company)
            {
                case "YGR":
                case "TST":
                case "UBT":
                case "SXN":
                default:
                    or1.CommandText = "INSERT INTO KUNDADR ( KDAMANDANT,KDAKUNDNR,KDAADRSL,KDASTAKZ,KDASTADAT,KDAANREDE,KDANAME1,KDANAME2,KDASTRASSE1,KDASTRASSE2,KDALAND,KDAPLZ,KDAORT,KDABZ,KDABZBEZ,KDATEL,KDATELEX,KDAFAX,KDABTX,KDADATEX,KDAHOMEPAGE,KDAMAILADR,KDATTEX,KDASPRKZ,KDALO,KDALB,KDALP,KDABBN,KDALIEFNR,KDAPLZPOSTF,KDAPOSTFACH,KDACOUNTY,KDACITY,KDASTEUER,KDAREGMWST,KDAMWSTID,KDAMWSTID2,KDASPERRPJ,KDASTLAND,KDASTREGBZ,KDASTKREIS,KDASTORT,KDAILN,KDASHIPINVD,KDAEORI,KDAEORINL,KDAKTOGRP,KDAFILLER,KDAAENDZL,KDAAENDBEN,KDAAENDDAT,KDANEUBEN,KDANEUDAT)"
                    + " VALUES (:KDAMANDANT:,:KDAKUNDNR:,:KDAADRSL:,:KDASTAKZ:,:KDASTADAT:,:KDAANREDE:,:KDANAME1:,:KDANAME2:,:KDASTRASSE1:,:KDASTRASSE2:,:KDALAND:,:KDAPLZ:,:KDAORT:,:KDABZ:,:KDABZBEZ:,:KDATEL:,:KDATELEX:,:KDAFAX:,:KDABTX:,:KDADATEX:,:KDAHOMEPAGE:,:KDAMAILADR:,:KDATTEX:,:KDASPRKZ:,:KDALO:,:KDALB:,:KDALP:,:KDABBN:,:KDALIEFNR:,:KDAPLZPOSTF:,:KDAPOSTFACH:,:KDACOUNTY:,:KDACITY:,:KDASTEUER:,:KDAREGMWST:,:KDAMWSTID:,:KDAMWSTID2:,:KDASPERRPJ:,:KDASTLAND:,:KDASTREGBZ:,:KDASTKREIS:,:KDASTORT:,:KDAILN:,:KDASHIPINVD:,:KDAEORI:,:KDAEORINL:,:KDAKTOGRP:,:KDAFILLER:,:KDAAENDZL:,:KDAAENDBEN:,:KDAAENDDAT:,:KDANEUBEN:,:KDANEUDAT:)";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
            }
            Connect2Proxy(or1);

        }
        public DataSet QueryKUNDADR(DataRow dwData, int option = 1)
        {
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0:
                    or1.CommandText = "SELECT * FROM KUNDADR WHERE  ROWNUM<2 ";
                    SResult = Connect2Proxy(or1);
                    break;
                case 1:
                    or1.CommandText = "SELECT KDAKUNDNR||KDAADRSL AS KEY,P.* FROM KUNDADR P  "
                     + " WHERE  KDAMANDANT = :KDAMANDANT: "
                    + dwData["KDAKUNDNR"].ToString()
                    + dwData["KDAADRSL"].ToString();
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    SResult = Connect2Proxy(or1);
                    break;
                case 2:
                    or1.CommandText = "SELECT KR.* FROM VVLIEF JOIN KUNDADR   KR ON VVKUNDNRE= KDAKUNDNR AND KDAADRSL=VVADRSLL "
                    + " WHERE VVMANDANT=:VVMANDANT: AND VVLIEFSCH = :VVLIEFSCH:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    SResult = Connect2Proxy(or1);
                    break;
                case 3:
                    or1.CommandText = "SELECT DISTINCT VVLIEFSCH,P.* FROM KUNDADR P JOIN VVLIEF ON KDAKUNDNR=VVKUNDNRE AND KDAADRSL=VVADRSLL"
                               + " WHERE VVMANDANT=:VVMANDANT: AND  VVLIEFSCH=:VVLIEFSCH:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    SResult = Connect2Proxy(or1);
                    break;
                case 5:
                    or1.CommandText = "SELECT  KDAKUNDNR||KDAADRSL AS KEY,M.* FROM KUNDADR M   WHERE  KDAMANDANT = :KDAMANDANT: AND KDANAME1 LIKE '%" + dwData["KDANAME1"].ToString() + "%' AND KDASTRASSE1 LIKE '%" + dwData["KDASTRASSE1"].ToString() + "%'";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    SResult = Connect2Proxy(or1);
                    break;
                default:
                    break;
            }


            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public void DeleteKUNDADR(DataRow dwData, int option = 1)
        {
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM KUNDADR WHERE  KDAKUNDNR = :KDAKUNDNR: AND KDAADRSL=:KDAADRSL:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case 2:
                    or1.CommandText = "DELETE FROM KUNDADR WHERE  KDAKUNDNR = :KDAKUNDNR:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }

        public void DKUNDADR00(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand(); 
            or1.CommandText = "DELETE FROM KUNDADR WHERE  KDAMANDANT = :KDAMANDANT:  ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData); 
            Connect2Proxy(or1);
        }

        public void UpdateKUNDADR(DataRow dwData, string ITXDBVERSION = "50.24", int option = 1)
        {
            if (dwData != null)
            {
                dwData["KDAAENDBEN"] = app.User;
                dwData["KDAAENDDAT"] = date;
            }

            or1 = OracleDB.CreateCommand();
            if (option == 0)
            {
                switch (app.company)
                {
                    case "YGR":
                    case "TST":
                    case "UBT":
                    case "SXN":
                    default:
                        option = 1;
                        break;
                }
            }
            switch (option)
            {
                case 1:
                    or1.CommandText = "UPDATE KUNDADR SET KDAMANDANT=:KDAMANDANT:,KDAKUNDNR=:KDAKUNDNR:,KDAADRSL=:KDAADRSL:,KDASTAKZ=:KDASTAKZ:,KDASTADAT=:KDASTADAT:,KDAANREDE=:KDAANREDE:,KDANAME1=:KDANAME1:,KDANAME2=:KDANAME2:,KDASTRASSE1=:KDASTRASSE1:,KDASTRASSE2=:KDASTRASSE2:,KDALAND=:KDALAND:,KDAPLZ=:KDAPLZ:,KDAORT=:KDAORT:,KDABZ=:KDABZ:,KDABZBEZ=:KDABZBEZ:,KDATEL=:KDATEL:,KDATELEX=:KDATELEX:,KDAFAX=:KDAFAX:,KDABTX=:KDABTX:,KDADATEX=:KDADATEX:,KDAHOMEPAGE=:KDAHOMEPAGE:,KDAMAILADR=:KDAMAILADR:,KDATTEX=:KDATTEX:,KDASPRKZ=:KDASPRKZ:,KDALO=:KDALO:,KDALB=:KDALB:,KDALP=:KDALP:,KDABBN=:KDABBN:,KDALIEFNR=:KDALIEFNR:,KDAPLZPOSTF=:KDAPLZPOSTF:,KDAPOSTFACH=:KDAPOSTFACH:,KDACOUNTY=:KDACOUNTY:,KDACITY=:KDACITY:,KDASTEUER=:KDASTEUER:,KDAREGMWST=:KDAREGMWST:,KDAMWSTID=:KDAMWSTID:,KDAMWSTID2=:KDAMWSTID2:,KDASPERRPJ=:KDASPERRPJ:,KDASTLAND=:KDASTLAND:,KDASTREGBZ=:KDASTREGBZ:,KDASTKREIS=:KDASTKREIS:,KDASTORT=:KDASTORT:,KDAILN=:KDAILN:,KDASHIPINVD=:KDASHIPINVD:,KDAEORI=:KDAEORI:,KDAEORINL=:KDAEORINL:,KDAKTOGRP=:KDAKTOGRP:,KDAFILLER=:KDAFILLER:,KDAAENDZL=:KDAAENDZL:,KDAAENDBEN=:KDAAENDBEN:,KDAAENDDAT=:KDAAENDDAT:,KDANEUBEN=:KDANEUBEN:,KDANEUDAT=:KDANEUDAT:"
                        + "  WHERE  KDAKUNDNR = :KDAKUNDNR: AND KDAADRSL=:KDAADRSL:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case 2:
                    or1.CommandText = "UPDATE KUNDADR SET ,KDAKUNDNR=:KDAADRSL:"
                        + "  WHERE  KDAMANDANT=:KDAMANDANT: AND KDAKUNDNR = :KDAKUNDNR: ";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }
    }
}
